package com.tracker.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import com.tracker.bean.AccountUser;

public class AccountUserDAO {

	private Connection conn = null;
	private ResultSet rs = null;
	private Statement stat = null;

	public Set<AccountUser> getListObject() throws Exception {
		Set<AccountUser> list = new HashSet<AccountUser>();
		conn = DBSession.getIstance().getConnection();
		String sql = " select id, firstname, lastname, date, email, icon, login, password, telcontact " +
					 " from t02_accountuser " +
					 " order by firstname, lastname ";
		
		stat = conn.createStatement();
		rs = stat.executeQuery(sql);
		while (rs.next()) 
			list.add(getObjectValue(rs));

		rs.close();
		stat.close();
		
		return list;
	}

	public void delete(String key) throws Exception {
		conn = DBSession.getIstance().getConnection();
		String sql = "delete from t02_accountuser where login = '"+ key +"'";
		
		stat = conn.createStatement();
		stat.execute(sql);

		stat.close();
	}

	public AccountUser getObjectByKey(String key) throws Exception {
		conn = DBSession.getIstance().getConnection();
		String sql = " select id, firstname, lastname, date, email, icon, login, password, telcontact " +
					 " from t02_accountuser " +
					 " where login = '" + key + "'";

		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(sql);
		rs.next();
		
		return getObjectValue(rs);
	}

	public AccountUser getObjectById(String key) throws Exception {
		conn = DBSession.getIstance().getConnection();
		String sql = " select id, firstname, lastname, date, email, icon, login, password, telcontact " +
					 " from t02_accountuser " +
					 " where id = " + key;

		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(sql);
		rs.next();
		
		return getObjectValue(rs);
	}

	private AccountUser getObjectValue(ResultSet rs) throws Exception{
	
		AccountUser accountUser = new AccountUser();
		accountUser.setId(rs.getLong("id"));
		accountUser.setFirstName(rs.getString("firstname"));
		accountUser.setLastName(rs.getString("lastname"));
		accountUser.setDate(rs.getString("date"));
		accountUser.setEmail(rs.getString("email"));
		accountUser.setIcon(rs.getString("icon"));
		accountUser.setLogin(rs.getString("login"));
		accountUser.setPassword(rs.getString("password"));
		accountUser.setTelContact(rs.getString("telcontact"));

		return accountUser;
	}
	
	public void persist(AccountUser accountUser) throws Exception {

		conn = DBSession.getIstance().getConnection();
		String sql = "insert into t02_accountuser (firstname, lastname, date, email, icon, login, password, telcontact) values (?,?,?,?,?,?,?,?)";

		if (exist(accountUser)) {
			System.out.println(">>>>>>> LOG: accountUser.getId() = " + accountUser.getId());

			sql = "update t02_accountuser set " + 
				  "		firstname = ?, " +
				  " 	lastname = ?, " +
				  " 	date = ?, " +
				  " 	email = ?, " +
				  " 	icon = ?, " +
				  " 	login = ?, " +
				  " 	password = ?, " +
				  " 	telcontact = ? " +
				  " where " +
				  "		id = " + String.valueOf(accountUser.getId());		
		}
		
		PreparedStatement stat = conn.prepareStatement(sql);
		stat.setString(1, accountUser.getFirstName());
		stat.setString(2, accountUser.getLastName());
		stat.setString(3, accountUser.getDate());
		stat.setString(4, accountUser.getEmail());
		stat.setString(5, accountUser.getIcon());
		stat.setString(6, accountUser.getLogin());
		stat.setString(7, accountUser.getPassword());
		stat.setString(8, accountUser.getTelContact());
		stat.execute();

	}

	public boolean exist(AccountUser accountUser) {
		boolean exist = false;
		conn = DBSession.getIstance().getConnection();
		try {
			String sql = "select id from t02_accountuser where login = '" + accountUser.getLogin() + "'";

			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			if (rs.next()) {
				accountUser.setId(rs.getLong("id"));
				exist = true;
			}
			

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				rs.close();
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return exist;
	}

}
